package services.system;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.*;
import procomponent.Excelformat;
import vo.system.Y_file;
import com.*;
import com.util.Util;
/**
 * PersonMgrFacade：上传services
 * @author beijunhua
 * @version Sun April 10 17:01:57 CST 2016 zhd
 * @author ($Date$ modification by $Author$)
 * @since 1.0
 */ 
public class UploadServices {
	static PublicSystem sys = PublicSystem.getInstance();
	/**
	 * 新增附件信息
	 * @author gxf 
	 * @param obj
	 * @throws Exception
	 */
	public void insertFile(HashMap obj) throws Exception {
		String filename = obj.get("filename").toString();
		String fileurl = obj.get("fileurl").toString();
		String czr = obj.get("czr").toString();
		String czsj = obj.get("czsj").toString();
		int id = sys.getId("SEQ_Y_FILE");
		Y_file vo = new Y_file();
		obj.put("id", id);
		vo = (Y_file)Util.MapToVo(vo, obj);
		vo.setFLAG("0");
		java.util.Date de = new Date();
		java.text.DateFormat dated = new java.text.SimpleDateFormat("yyyy-MM-dd_HH:mm:ss");
		String s_time = dated.format(de);
		String sbrq = s_time.substring(0,4)+"-01-01";
		long sTime = 0;
		long eTime = 0;
		long Time = 0;
		sTime = System.currentTimeMillis();
		List list = Excelformat.readExcel(new File(Const.server_path + fileurl));
		eTime = System.currentTimeMillis();
		Time = eTime - sTime;     
		insertsql(list);
		System.out.println("/n******上载解析完毕,数据量：" + list.size() + "条,共耗时:" + Time + "毫秒******");
		sys.Jdbc_saveDao(vo);
	}
	/**
	 * 插入sql语句
	 * @param list
	 * @throws Exception
	 */
    public static void insertsql(List list) throws Exception {
    	int num = 0;int count = 0;
    	Connection con = sys.getConnection();
    	int size = list.size();
    	int id = sys.getId("SEQ_Y_AC01");
    	PreparedStatement stmt = con.prepareStatement("insert into ac01_ls(aac001,aac003,aac058,aac147,aac006,aac004,aac005) values (?,?,?,?,?,?,?)");
    	try {   
    		for(int i=1;i<size;i++)
    	{		
		count ++;  
		String ss = list.get(i).toString();
		ss = ss.substring(1, ss.length()-1);
		String res[] = ss.split(",");
		stmt.setString(1, String.valueOf(id));
		stmt.setString(2, res[0]);
		stmt.setString(3, res[1]);
		stmt.setString(4, res[2]);
		stmt.setString(5, res[3]);
		stmt.setString(6, res[4]);
		stmt.setString(7, res[5]);
		stmt.addBatch();
		if(count%2000 == 0) {
			//批处理再提交  
			stmt.executeBatch();
			con.commit();
			stmt.close();
			con.close();
			con = sys.getConnection();
			stmt = con.prepareStatement("insert into ac01_ls(aac001,aac003,aac058,aac147,aac006,aac004,aac005) values (?,?,?,?,?,?,?)");
		}
	} 
			stmt.executeBatch();
			con.commit();
			stmt.close();
			con.close();
		} catch(Exception d) {	
			con.rollback();
			stmt.close();
			con.close();
			d.printStackTrace();
		}
		finally {
			if (stmt != null) {  
				stmt.close();  
			} if (con != null && !con.isClosed()) {  
				con.close();  
			}  
		}
	}
	/**
	 * 根据ywid和文件名获取附件信息
	 * @param list
	 * @return
	 * @throws Exception
	 */
	public List getFileMap(String filename) throws Exception {
		String sql = "select * from y_file t where  filename='"  + filename + "'";
		List resulet = sys.SelResultList(sql);
		return resulet;
	}
	/**
	 * 读取表格
	 * @param filename
	 * @param dwm
	 * @throws Exception
	 */
	public void readExcel(String filename,String dwm) throws Exception {
		String filePath = "D:/aa2.csv";
		File file = new File(filePath);
		if (file.exists()) {
			int i = 0;
			String strLine = null;
			Statement ps = null;
			Connection con = null;
			String sql = null;
			try {
				con = sys.getConnection();
				con.setAutoCommit(false);
				ps = con.createStatement();
				BufferedReader bufferedreader = new BufferedReader(new FileReader(filePath));
				while ((strLine = bufferedreader.readLine()) != null) {
					i++;
					String [] values = strLine.split(",");//逗号隔开的各个列
					String cell0 = values[0];
					String cell1 = values[1];
					String cell2 = values[2];
					String cell3 = values[3];
					String cell4 = values[4];
					String cell5 = values[5];
					String cell6 = values[6];
					String cell7 = values[7];	
					sql = String.format("INSERT INTO TBLDATA2(CELL0,CELL1,CELL2,CELL3,CELL4,CELL5,CELL6,CELL7,TID) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s',SEQ_TBLID.NEXTVAL)",
							cell0, cell1, cell2, cell3, cell4, cell5, cell6, cell7);
					ps.executeUpdate(sql);
					if(i%2000 == 0) {
						//500条记录提交一次
						con.commit();
						System.out.println("已成功提交"+i+"行!");
					}
				}
				if(i%2000!= 0) {
					//不够500条的再提交一次（其实不用判断，直接提交就可以，不会重复提交的）
					con.commit();
					System.out.println("已成功提交" + i + "行!");
				}
			} catch(Exception ex) {
				System.out.println("导出第" + (i+1) + "条时出错，数据是" + strLine);
				System.out.println("出错的sql语句是：" + sql);
				System.out.println("错误信息：");
				ex.printStackTrace();
				try {
					if (con != null) {
						con.rollback();
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			} finally {
				try {
					if (ps != null) {
						ps.close();
					}
				} catch (Exception ex) {
					ex.printStackTrace();
				}
				try {
					if (con != null){
						con.close();
					}
				} catch (Exception ex) {
					ex.printStackTrace();
				}
			}
		}
	}
}

